﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Common;
namespace childcare
{
    public partial class frmchildren : Form
    {
        SqlConnection con = new SqlConnection();
        private DataTable dt = new DataTable();
        private SqlDataAdapter da = new SqlDataAdapter();
        private Boolean kt;
        public frmchildren()
        {
            InitializeComponent();
        }

        private void btnexit_Click(object sender, EventArgs e)
        {
            this.Close();
            frmguest _frmtrangphu = new frmguest();
            _frmtrangphu.Show();
            
        }

        private void frmchildren_Load(object sender, EventArgs e)//select du lieu cua bang children ra 
        {
            // TODO: This line of code loads data into the 'childcareDataSet._class' table. You can move, or remove it, as needed.
            this.classTableAdapter.Fill(this.childcareDataSet._class);
            string connect = @"Data Source=BLACK-PC\SQLEXPRESS;Initial Catalog=Childcare;Integrated Security=True";
            con = new SqlConnection(connect);
            connectdatabase.Openconnect();
            dgvchildren.DataSource = connectdatabase.ex("select * from children");
            databinding();
            
            
        }
       
        private void databinding()//bing dinh du lieu
        {
            txtmahocsinh.DataBindings.Clear();
            txtmahocsinh.DataBindings.Add("Text", dgvchildren.DataSource, "C_ID");
            txthoten.DataBindings.Clear();
            txthoten.DataBindings.Add("Text", dgvchildren.DataSource, "name");
            txtngaysinh.DataBindings.Clear();
            txtngaysinh.DataBindings.Add("Text", dgvchildren.DataSource, "date");
            txtgioitinh.DataBindings.Clear();
            txtgioitinh.DataBindings.Add("Text", dgvchildren.DataSource, "sex");
            cbolophoc.DataBindings.Clear();
            cbolophoc.DataBindings.Add("Text", dgvchildren.DataSource, "CL_ID");
            txttieusu.DataBindings.Clear();
            txttieusu.DataBindings.Add("Text", dgvchildren.DataSource, "profile");
            txtsothich.DataBindings.Clear();
            txtsothich.DataBindings.Add("Text", dgvchildren.DataSource, "interests");
        }

        private void lbl1_Click(object sender, EventArgs e)
        {

        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox4_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox5_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {

        }

        private void btnhuy_Click(object sender, EventArgs e)
        {
            txtmahocsinh.Clear();
            txthoten.Clear();
            txtgioitinh.Clear();
            txtngaysinh.Clear();
            cbolophoc.DataBindings.Clear();
            txttieusu.Clear();
            txtsothich.Clear();
            databinding();
        }
        // tao ham kiem tra 
        private bool test()
        {
            kt = true;
            if (txtmahocsinh.Text == "")
            {
                MessageBox.Show("Bạn phải nhập mã số của học sinh", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
                txtmahocsinh.Focus();
            }
            if (txthoten.Text =="")
            {
                MessageBox.Show("Bạn phải nhập họ tên của học sinh", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
                txthoten.Focus();
            }
            return kt;
        }
        private void btnthem_Click(object sender, EventArgs e)
        {
            test();
            if (kt == true)
            {
                try
                {
                    SqlCommand commandinsert = new SqlCommand();
                    commandinsert.Connection = con;
                    commandinsert.CommandType = CommandType.Text;
                    commandinsert.CommandText = "insert children (C_ID,name,sex,date,CL_ID,profile,interests) values(@C_ID,@name, @sex, @date, @CL_ID, @profile, @interests)";
                    SqlParameter pram = commandinsert.Parameters.Add("@C_ID", SqlDbType.VarChar, 6);
                    pram.Value = txtmahocsinh.Text;
                    pram = commandinsert.Parameters.Add("@name", SqlDbType.NVarChar, 25);
                    pram.Value = txthoten.Text;
                    pram = commandinsert.Parameters.Add("@sex", SqlDbType.NVarChar, 5);
                    pram.Value = txtgioitinh.Text;
                    pram = commandinsert.Parameters.Add("@date", SqlDbType.SmallDateTime);
                    pram.Value = txtngaysinh.Text;
                    pram = commandinsert.Parameters.Add("@CL_ID", SqlDbType.VarChar, 6);
                    pram.Value = cbolophoc.Text;
                    pram = commandinsert.Parameters.Add("@profile", SqlDbType.NVarChar, 300);
                    pram.Value = txttieusu.Text;
                    pram = commandinsert.Parameters.Add("@interests", SqlDbType.NVarChar, 100);
                    pram.Value = txtsothich.Text;
                    con.Open();
                    commandinsert.ExecuteNonQuery();
                    dgvchildren.DataSource = connectdatabase.ex("select * from children");   
                    MessageBox.Show("Bạn đã thêm thành công!", "Thông Báo", MessageBoxButtons.OK);
                    databinding();
                }
                catch// (Exception ex)
                {
                    MessageBox.Show("Mã học sinh da tồn tại", "Thông Báo", MessageBoxButtons.OK);
                    //return;
                }
                finally
                {
                    con.Close();
                }
                
            }

        }


        private void btnxoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có thực sự muốn xóa không ?", "XÓA", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                SqlCommand commandelete = new SqlCommand();
                commandelete.Connection = con;
                commandelete.CommandType = CommandType.Text;
                commandelete.CommandText = @"delete from hoc_phi where C_ID = @C_ID"; 
                SqlParameter pram = commandelete.Parameters.Add("@C_ID", SqlDbType.VarChar, 6);
                pram.Value = txtmahocsinh.Text;
                con.Open();
                commandelete.ExecuteNonQuery();
                commandelete.CommandText = @"delete from children where C_ID = @C_ID ";
                commandelete.ExecuteNonQuery();
                con.Close();
                dgvchildren.DataSource = connectdatabase.ex("select * from children");
                MessageBox.Show("Bạn đã Xóa thành công!", "Thông Báo", MessageBoxButtons.OK);
                databinding();
            }
           
        }

        private void btnsua_Click(object sender, EventArgs e)
        {
            test();
            if (kt == true)
            {
                SqlCommand commandupdate = new SqlCommand();
                commandupdate.Connection = con;
                commandupdate.CommandType = CommandType.Text;
                commandupdate.CommandText = @"update children set name = @name,sex = @sex,date = @date,profile = @profile,interests = @interests, CL_ID = @CL_ID where C_ID = @C_ID";
                SqlParameter pram = commandupdate.Parameters.Add("@C_ID", SqlDbType.VarChar, 6);
                pram.Value = txtmahocsinh.Text;
                pram = commandupdate.Parameters.Add("@name", SqlDbType.NVarChar, 25);
                pram.Value = txthoten.Text;
                pram = commandupdate.Parameters.Add("@sex", SqlDbType.NVarChar, 5);
                pram.Value = txtgioitinh.Text;
                pram = commandupdate.Parameters.Add("@date", SqlDbType.SmallDateTime);
                pram.Value = txtngaysinh.Text;
                pram = commandupdate.Parameters.Add("@CL_ID", SqlDbType.VarChar, 6);
                pram.Value = cbolophoc.Text;
                pram = commandupdate.Parameters.Add("@profile", SqlDbType.NVarChar, 300);
                pram.Value = txttieusu.Text;
                pram = commandupdate.Parameters.Add("@interests", SqlDbType.NVarChar, 100);
                pram.Value = txtsothich.Text;
                con.Open();
                commandupdate.ExecuteNonQuery();
                commandupdate.CommandText = @"update hoc_phi set C_ID = @C_ID, [name] = @name, CL_ID = @CL_ID  where C_ID = @C_ID";
                commandupdate.ExecuteNonQuery();
                con.Close();
                dgvchildren.DataSource = connectdatabase.ex("select * from children");
                MessageBox.Show("Bạn đã Sửa thành công!", "Thông Báo", MessageBoxButtons.OK);
                databinding();
            }
        }

        private void lblgioitinh_Click(object sender, EventArgs e)
        {

        }

        private void lbltiensubenh_Click(object sender, EventArgs e)
        {

        }

        private void btntimkiem_Click(object sender, EventArgs e)
        {
            dt.Clear();
            SqlCommand commandsearch = new SqlCommand();
            commandsearch.Connection = con;
            commandsearch.CommandType = CommandType.Text;
            commandsearch.CommandText = "select * from children where name=@name or C_ID = @C_ID";
            SqlParameter pram = commandsearch.Parameters.Add("@name", SqlDbType.NVarChar, 25);
            pram.Value = txttkten.Text;
            pram = commandsearch.Parameters.Add("@C_ID", SqlDbType.VarChar, 6);
            pram.Value = txttkten.Text;
            da.SelectCommand = commandsearch;
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dgvchildren.DataSource = dt;
            }
            else
            {
                dgvchildren.DataSource = dt;
                MessageBox.Show("Không tìm thấy sinh viên này!", "THÔNG BÁO", MessageBoxButtons.OK);
            }
            databinding();
        }

        private void cbogioitinh_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void dgvchildren_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void btn_in_Click(object sender, EventArgs e)
        {
            frm_test _frm = new frm_test();
            _frm.Show();



        }
    }
}
